using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Decision.BusinessLayer.DataLayer
{
	/// <summary>
	/// Data access layer class for ECT_USER
	/// </summary>
	class ECT_USERSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public ECT_USERSql()
		{
			// Nothing for now.
		}

        #endregion

        /// <summary>
        /// Select By Criteria
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public List<ECT_USER> SelectByCriteria(ECT_USER criteria)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM ECT_USER WHERE 1=1 ";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                if (criteria.USER_GROUP_LVL != null && criteria.USER_GROUP_LVL != -1)
                {
                    sqlCommand.CommandText += " AND USER_GROUP_LVL = @UserGroupLvl";
                }

                if (criteria.USERNAME != null && criteria.USERNAME != "")
                {
                    sqlCommand.CommandText += " AND USERNAME = @USER_NAME";
                }

                if (criteria.FNAME != null && criteria.FNAME != "")
                {
                    sqlCommand.CommandText += " AND ( FNAME LIKE '%'+@FNAME+'%' OR  LNAME LIKE '%'+@FNAME+'%' )";
                }

                //
                if (criteria.USER_GROUP_LVL != null && criteria.USER_GROUP_LVL != -1)
                {
                    sqlCommand.Parameters.Add(new SqlParameter("@UserGroupLvl", criteria.USER_GROUP_LVL));
                }

                if (criteria.USERNAME != null && criteria.USERNAME != "")
                {
                    sqlCommand.Parameters.Add(new SqlParameter("@USER_NAME", criteria.USERNAME));
                }

                if (criteria.FNAME != null && criteria.FNAME != "")
                {
                    sqlCommand.Parameters.Add(new SqlParameter("@FNAME", criteria.FNAME));
                }


                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::SelectByField::Error occured." + ex.ToString(), ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(ECT_USER businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
			sqlCommand.CommandText = "dbo.[ECT_USER_Insert]";
			sqlCommand.CommandType = CommandType.StoredProcedure;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@USER_ID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@USERNAME", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USERNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@PASSWORD", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PASSWORD));
				sqlCommand.Parameters.Add(new SqlParameter("@IS_ACTIVE", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IS_ACTIVE));
				sqlCommand.Parameters.Add(new SqlParameter("@FNAME", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@LNAME", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@TEL_NO", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TEL_NO));
				sqlCommand.Parameters.Add(new SqlParameter("@DATE_OF_BIRTH", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DATE_OF_BIRTH));
				sqlCommand.Parameters.Add(new SqlParameter("@ADDRESS", SqlDbType.Text, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ADDRESS));
				sqlCommand.Parameters.Add(new SqlParameter("@SEX", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.SEX));
				sqlCommand.Parameters.Add(new SqlParameter("@USER_GROUP_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_GROUP_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@PROVINCE_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PROVINCE_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@USER_GROUP_LVL", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_GROUP_LVL));

								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                businessObject.USER_ID = (int)sqlCommand.Parameters["@USER_ID"].Value;

				return true;
			}
			catch(Exception ex)
			{				
				throw new Exception("ECT_USER::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(ECT_USER businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_Update]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@USER_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@USERNAME", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USERNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@PASSWORD", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PASSWORD));
				sqlCommand.Parameters.Add(new SqlParameter("@IS_ACTIVE", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IS_ACTIVE));
				sqlCommand.Parameters.Add(new SqlParameter("@FNAME", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@LNAME", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LNAME));
				sqlCommand.Parameters.Add(new SqlParameter("@TEL_NO", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TEL_NO));
				sqlCommand.Parameters.Add(new SqlParameter("@DATE_OF_BIRTH", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DATE_OF_BIRTH));
				sqlCommand.Parameters.Add(new SqlParameter("@ADDRESS", SqlDbType.Text, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ADDRESS));
				sqlCommand.Parameters.Add(new SqlParameter("@SEX", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.SEX));
				sqlCommand.Parameters.Add(new SqlParameter("@USER_GROUP_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_GROUP_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@PROVINCE_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PROVINCE_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@USER_GROUP_LVL", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.USER_GROUP_LVL));

                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }


        public ECT_USER SelectByUserPassword(string p1, string p2)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM ECT_USER WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@USERNAME", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, p1));
                sqlCommand.Parameters.Add(new SqlParameter("@PASSWORD", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, p2));

                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    ECT_USER businessObject = new ECT_USER();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::SelectByUserPassword::Error occured."+ex, ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>ECT_USER business object</returns>
        public ECT_USER SelectByPrimaryKey(ECT_USERKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_SelectByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@USER_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.USER_ID));

                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    ECT_USER businessObject = new ECT_USER();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of ECT_USER</returns>
        public List<ECT_USER> SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_SelectAll]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {                
                throw new Exception("ECT_USER::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of ECT_USER</returns>
        public List<ECT_USER> SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_SelectByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(ECT_USERKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_DeleteByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@USER_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.USER_ID));


                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("ECT_USER::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[ECT_USER_DeleteByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch (Exception ex)
            {                
                throw new Exception("ECT_USER::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(ECT_USER businessObject, IDataReader dataReader)
        {


				businessObject.USER_ID = dataReader.GetInt32(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USER_ID.ToString()));

				businessObject.USERNAME = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USERNAME.ToString()));

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.PASSWORD.ToString())))
				{
					businessObject.PASSWORD = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.PASSWORD.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.IS_ACTIVE.ToString())))
				{
					businessObject.IS_ACTIVE = dataReader.GetBoolean(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.IS_ACTIVE.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.FNAME.ToString())))
				{
					businessObject.FNAME = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.FNAME.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.LNAME.ToString())))
				{
					businessObject.LNAME = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.LNAME.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.TEL_NO.ToString())))
				{
					businessObject.TEL_NO = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.TEL_NO.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.DATE_OF_BIRTH.ToString())))
				{
					businessObject.DATE_OF_BIRTH = dataReader.GetDateTime(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.DATE_OF_BIRTH.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.ADDRESS.ToString())))
				{
					businessObject.ADDRESS = dataReader.GetString(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.ADDRESS.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.SEX.ToString())))
				{
					businessObject.SEX = dataReader.GetInt32(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.SEX.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USER_GROUP_ID.ToString())))
				{
					businessObject.USER_GROUP_ID = dataReader.GetInt32(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USER_GROUP_ID.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.PROVINCE_ID.ToString())))
				{
					businessObject.PROVINCE_ID = dataReader.GetInt32(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.PROVINCE_ID.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USER_GROUP_LVL.ToString())))
				{
					businessObject.USER_GROUP_LVL = dataReader.GetInt32(dataReader.GetOrdinal(ECT_USER.ECT_USERFields.USER_GROUP_LVL.ToString()));
				}


        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of ECT_USER</returns>
        internal List<ECT_USER> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<ECT_USER> list = new List<ECT_USER>();

            while (dataReader.Read())
            {
                ECT_USER businessObject = new ECT_USER();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

    }
}
